/**
 * 
 */
package icst.cyber.hrm.mapper;

import icst.cyber.hrm.dto.AccountDTO;
import icst.cyber.hrm.dto.EmpWorkDTO;
import icst.cyber.hrm.dto.EmployeeDTO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author NGOCLAN
 * 
 */
public class PersonMapperDB extends MapperDB {

	/**
	 * This constructor creates a new connection
	 * 
	 * @throws Exception
	 */
	public PersonMapperDB() throws Exception {
		super();
	}

	/**
	 * This constructor uses the existing connection
	 * 
	 * @param con
	 */
	public PersonMapperDB(Connection con) {
		super(con);
	}

	/**
	 * Get one account
	 * 
	 * @param username
	 * @param password
	 * @return AccountDTO
	 */
	public AccountDTO getOneAccount(String username, String password) {
		log.debug("PersonMapperDB.getOneAccount.start");

		AccountDTO accDTO = null;
		String query = "";
		ResultSet rs = null;
		PreparedStatement pStat = null;

		try {
			query = "select * from account where username=? and password=?";
			pStat = getConnection().prepareStatement(query);
			pStat.setString(1, username);
			pStat.setString(2, password);
			rs = pStat.executeQuery();

			while (rs.next()) {
				accDTO = new AccountDTO();
				accDTO.setId(rs.getInt("id"));
				accDTO.setId_emp(rs.getInt("id_emp"));
				accDTO.setUsername(rs.getString("username"));
				accDTO.setPassword(rs.getString("password"));
			}
		} catch (Exception ex) {
			log.error("PersonMapperDB.getOneAccount", ex);
		} finally {
			try {
				rs.close();
				pStat.close();
			} catch (SQLException sqlEx) {
				log.error("PersonMapperDB.getOneAccount", sqlEx);
			}
		}

		log.debug("PersonMapperDB.getOneAccount.end");
		return accDTO;
	}

	public AccountDTO getOneAccount(int id_emp) {
		log.debug("PersonMapperDB.getOneAccount.start");

		AccountDTO accDTO = null;
		String query = "";
		ResultSet rs = null;
		PreparedStatement pStat = null;

		try {
			query = "select * from account where id_emp=?";
			pStat = getConnection().prepareStatement(query);
			pStat.setInt(1, id_emp);
			rs = pStat.executeQuery();

			while (rs.next()) {
				accDTO = new AccountDTO();
				accDTO.setId(rs.getInt("id"));
				accDTO.setId_emp(rs.getInt("id_emp"));
				accDTO.setUsername(rs.getString("username"));
				accDTO.setPassword(rs.getString("password"));
			}
		} catch (Exception ex) {
			log.error("PersonMapperDB.getOneAccount", ex);
		} finally {
			try {
				rs.close();
				pStat.close();
			} catch (SQLException sqlEx) {
				log.error("PersonMapperDB.getOneAccount", sqlEx);
			}
		}

		log.debug("PersonMapperDB.getOneAccount.end");
		return accDTO;
	}

	/**
	 * Get one employee
	 * 
	 * @param id_emp
	 * @return EmployeeDTO
	 */
	public EmployeeDTO getOneEmployee(int id_emp) {
		log.debug("PersonMapperDB.getOneEmployee.start");

		EmployeeDTO empDTO = null;
		String query = "";
		ResultSet rs = null;
		PreparedStatement pStat = null;

		try {
			query = "select * from employee where id=?";
			pStat = getConnection().prepareStatement(query);
			pStat.setInt(1, id_emp);
			rs = pStat.executeQuery();

			while (rs.next()) {
				empDTO = new EmployeeDTO();
				empDTO.setId(rs.getInt("id"));
				empDTO.setName(rs.getString("name"));
				empDTO.setGender(rs.getBoolean("gender"));
				empDTO.setBirthdate(rs.getString("birthdate"));
				empDTO.setAddress(rs.getString("address"));
				empDTO.setId_city(rs.getInt("id_city"));
				empDTO.setId_marital(rs.getInt("id_marital"));
				empDTO.setHomephone(rs.getString("homephone"));
				empDTO.setCellphone(rs.getString("cellphone"));
				empDTO.setEmail(rs.getString("email"));
			}
		} catch (Exception ex) {
			log.error("PersonMapperDB.getOneEmployee" + ex);
		} finally {
			try {
				rs.close();
				pStat.close();
			} catch (SQLException sqlEx) {
				log.error("PersonMapperDB.getOneEmployee", sqlEx);
			}
		}

		log.debug("PersonMapperDB.getOneEmployee.end");
		return empDTO;
	}

	/**
	 * Get one empWork
	 * 
	 * @param id_emp
	 * @return EmpWorkDTO
	 */
	public EmpWorkDTO getOneEmpWork(int id_emp) {
		log.debug("PersonMapperDB.getOneEmpWork.start");

		EmpWorkDTO workDTO = null;
		String query = "";
		ResultSet rs = null;
		PreparedStatement pStat = null;

		try {
			query = "select * from empwork where id_emp=?";
			pStat = getConnection().prepareStatement(query);
			pStat.setInt(1, id_emp);
			rs = pStat.executeQuery();

			while (rs.next()) {
				workDTO = new EmpWorkDTO();
				workDTO.setId(rs.getInt("id"));
				workDTO.setId_emp(rs.getInt("id_emp"));
				workDTO.setId_role(rs.getInt("id_role"));
				workDTO.setId_job(rs.getInt("id_job"));
				workDTO.setSalary(rs.getFloat("salary"));
				workDTO.setStartdate(rs.getString("startdate"));
				workDTO.setStatus(rs.getBoolean("status"));
			}
		} catch (Exception ex) {
			log.error("PersonMapperDB.getOneEmpWork" + ex);
		} finally {
			try {
				rs.close();
				pStat.close();
			} catch (SQLException sqlEx) {
				log.error("PersonMapperDB.getOneEmpWork", sqlEx);
			}
		}

		log.debug("PersonMapperDB.getOneEmpWork.end");
		return workDTO;
	}

}
